<?php  if ( ! defined('BASEPATH')) exit('No direct script access allowed');
/**
 * login
 *
 * @author Simon Emms <simon@simonemms.com>
 */
class database extends MY_Model {
    
    
    
    /**
     * Supported Databases
     * 
     * @var array
     */
    protected $_arrDBTypes = array(
        'mysql' => 'MySQL',
        'mssql' => 'MS-SQL',
        'postgre' => 'PostgreSQL',
        'sqlite' => 'SQLite',
        //'sqlsrv' => 'SQL Azure', /* We can't change the DB as connection paired - investigate */
    );
    
    
    
    
    protected $_arrConnections = array();
    
    
    protected $_connection = false;
    
    
    
    
    public function __construct() {
        parent::__construct();
        
        $this->lang->load('database');
        
        $this->load->helper('database');
    }
    
    
    
    
    
    /**
     * Set DB Values
     * 
     * Sets the values for the DB
     * 
     * @param string $server
     * @param string $username
     * @param string $password
     * @param string $type
     * @param string $port
     * @return string
     */
    protected function _set_db_values($server, $username, $password, $type, $port = null) {
        /* Set the DB params */
        $arrDb = array(
            'hostname' => $server,
            'username' => $username,
            'password' => $password,
            'dbdriver' => $type,
            'database' => '',
            'pconnect' => true,
            'db_debug' => false,
            'cache_on' => false,
            'cachedir' => '',
            'char_set' => 'utf8',
            'dbcollat' => 'utf8_general_ci',
            'swap_pre' => '',
            'autoinit' => true,
            'stricton' => false,
        );
        
        /* Add the port */
        if(!is_null($port)) { $arrDb['port'] = $port; }
        
        return $arrDb;
    }
    
    
    
    
    
    
    /**
     * Use Connection
     * 
     * Specify which DB to use
     * 
     * @param string $connection
     * @return bool
     */
    protected function _use_connection($connection) {
        if(array_key_exists($connection, $this->_arrConnections)) {
            $this->_connection = $connection;
            return true;
        } else {
            $this->_connection = false;
            return false;
        }
    }
    
    
    
    
    
    
    /**
     * Use Database
     * 
     * Forces which database to be used
     * 
     * @param string $db
     * @return bool
     */
    protected function _use_database($db) { return $this->db()->use_database($db); }
    
    
    
    
    
    
    
    /**
     * Connection Name
     * 
     * Friendly name of the connection
     * 
     * @param array $arrConnection
     * @return string
     */
    public function connection_name($arrConnection = null) {
        $arrKeys = array('username', 'server');
        
        if(is_array($arrConnection) && array_keys_exist($arrKeys, $arrConnection)) {
            return $arrConnection['username'].'@'.$arrConnection['server'];
            
        } elseif(is_object($this->db())) {
            $arrConnection = array(
                'username' => $this->db()->username,
                'server' => $this->db()->hostname,
            );
            
            return $this->connection_name($arrConnection);
        } else {
            return null;
        }
    }
    
    
    
    
    
    
    /**
     * Close Connection
     * 
     * Closes the database connection
     * 
     * @param string $id
     */
    public function close_connection($id) {
        $arrSession = $this->session->userdata($this->config->item('session_login'));
        
        if(is_array($arrSession) && count($arrSession) > 0 && array_key_exists($id, $arrSession)) {
            /* Remove the connection */
            unset($arrSession[$id]);
        }
        
        /* Replace the session */
        $this->session->set_userdata($this->config->item('session_login'), $arrSession);
    }
    
    
    
    
    
    
    /**
     * DB
     * 
     * Fetch the correct DB connection. Although
     * you can access it publicly, you shouldn't
     * 
     * @param bool $return_id
     * @return string/object
     */
    public function db($return_id = false) {
        $count = count($this->_arrConnections);
        
        if($count > 0) {
            /* There are some connections */
            if($count == 1) {
                /* Just one - return that */
                if($return_id) {
                    /* Return the ID only */
                    return current(array_keys($this->_arrConnections));
                } else {
                    /* Return the object */
                    return current($this->_arrConnections);
                }
            } else {
                /* Multiple - is it set */
                if($this->_connection !== false) {
                    /* Yes */
                    $connection = $this->_connection;
                } else {
                    /* No - get the connection from the REST */
                    $connection = $this->input->rest($this->config->item('connection', 'rest'));
                }
                
                if($connection !== false) {
                    if(array_key_exists($connection, $this->_arrConnections)) {
                        if($return_id) {
                            /* Return the ID only */
                            return $connection;
                        } else {
                            /* Return the object */
                            return $this->_arrConnections[$connection];
                        }
                    }
                }
                
                /* Not specified/valid - use the first one in the array */
                if($return_id) {
                    /* Return the ID only */
                    return current(array_keys($this->_arrConnections));
                } else {
                    /* Return the object */
                    return current($this->_arrConnections);
                }
            }
        }
        show_error('There are no connections');
    }
    
    
    
    
    
    
    
    /**
     * DB Forge
     * 
     * Get the forge!
     * 
     * @return object
     */
    public function dbforge() {
        $id = $this->db(true);
        
        if(is_array($this->dbforge) && array_key_exists($id, $this->dbforge)) {
            return $this->dbforge[$id];
        }
    }
    
    
    
    
    
    
    
    /**
     * DBUtil
     * 
     * Get the DB Utility connection
     * 
     * @return object
     */
    public function dbutil() {
        $id = $this->db(true);
        
        if(is_array($this->dbutil) && array_key_exists($id, $this->dbutil)) {
            return $this->dbutil[$id];
        }
    }
    
    
    
    
    
    
    
    /**
     * Execute
     * 
     * This executes a raw SQL query.  Returns data based
     * upon the result
     * 
     * @param string $database
     * @param string $query
     * @param string $connection
     * @return array
     */
    public function execute($database = '', $query = '', $connection = null) {
        
        /* Start the benchmark */
        $this->benchmark->mark('db_execute_start');
        
        /* Set the connection */
        if(is_null($connection)) { $valid_connection = true; }
        else { $valid_connection = $this->_use_connection($connection); }
        
        /* Set the database */
        $valid_db = $this->_use_database($database);
        
        /* Set the return array */
        $arrReturn = array(
            'error' => false,
            'connection' => $this->db(true),
            'database' => $database,
            'query' => $query,
            'empty' => false,
            'data' => array(),
        );
        
        /* Invalid connection */
        if($valid_connection === false) {
            $arrReturn['error'] = true;
            $error = $this->lang->line('database_invalid_connection');
        }
        
        /* Invalid database */
        if($valid_db === false) {
            $arrReturn['error'] = true;
            $error = $this->lang->line('database_invalid_database');
        }
        
        if($arrReturn['error'] === false) {
            /* Run the query */
            $objDb = $this->db()->query($query);

            if(is_object($objDb)) {
                /* Valid query - has it returned anything */
                if($objDb->num_rows() > 0) {
                    /* Yes - run it as an array */
                    $arrDb = $this->db()->qAssoc($objDb);

                    /* Check empty result */
                    if($arrDb === false) { $arrDb = array(); }

                    $arrReturn['data'] = $arrDb;
                } else {
                    /* No - get the table structure */
                    $arrReturn['empty'] = true;
                }
            } else {
                /* Get the error message */
                $error = $this->db()->_error_message();

                if(empty($error)) { $error = $this->lang->line('database_error'); }

                $arrReturn['error'] = $error;
            }
        } else {
            /* Connection/DB error */
            $arrReturn['error'] = $error;
        }
        
        /* End the benchmark */
        $this->benchmark->mark('db_execute_end');
        
        $arrReturn['time'] = $this->benchmark->elapsed_time('db_execute_start', 'db_execute_end');
        
        return $arrReturn;
        
    }
    
    
    
    
    
    
    
    
    /**
     * Fetch Connections
     * 
     * Fetches the connections that are currently
     * logged in
     * 
     * @return array
     */
    public function fetch_connections() {
        $arrConnections = array();
        $arrSession = $this->session->userdata($this->config->item('session_login'));
        
        if(count($this->_arrConnections) > 0) {
            foreach(array_keys($this->_arrConnections) as $id) {
                if(array_key_exists($id, $arrSession)) {
                    $arrConnections[$id] = $arrSession[$id];
                }
            }
        }
        
        return $arrConnections;
    }
    
    
    
    
    
    
    
    
    /**
     * Fetch Databases
     * 
     * Fetch the databases
     * 
     * @param bool $key_as_db
     * @return array
     */
    public function fetch_databases($key_as_db = false) {
        
        $arrDb = $this->dbutil()->list_databases();
        
        if(is_array($arrDb) === false) { $arrDb = array(); }
        
        if(count($arrDb) > 0 && $key_as_db) {
            $arrReturn = array();
            foreach($arrDb as $db) {
                $arrReturn[$db] = $db;
            }
            $arrDb = $arrReturn;
        }
        
        return $arrDb;
    }
    
    
    
    
    
    
    
    
    /**
     * Fetch Structure
     * 
     * Fetches all the databases and tables available
     * 
     * @param string $url
     * @return array
     */
    public function fetch_structure($current_url = null) {
        /* Load stuff */
        $this->load->dbutil();
        
        /* Get the tables */
        $arrDatabase = $this->fetch_databases();
        
        /* Alphabetise - might not be in alphabetical order */
        sort($arrDatabase);
        
        $arrReturn = array();
        if(is_array($arrDatabase) && count($arrDatabase) > 0) {
            foreach($arrDatabase as $db) {
                /* Delete the cache so it requeries the tables */
                unset($this->db()->data_cache['table_names']);
                
                /* Set the database */
                $this->_use_database($db);
                
                /* Get the list */
                $arrTables = $this->db()->list_tables();
                
                $arrChildren = array();
                
                if(is_null($current_url)) { $current_url = current_url(); }
                
                /* Get the URL */
                $rest_url = $this->input->rest($this->config->item('database', 'rest'), false, $current_url);
                
                if($rest_url == $db) {
                    /* Remove the DB from URL */
                    $db_url = site_url();
                    $override = true;
                } else {
                    $db_url = $this->input->rest_replace($this->config->item('database', 'rest'), $db, $current_url);
                    $override = false;
                }
                
                if(is_array($arrTables) && count($arrTables) > 0) {
                    foreach($arrTables as $table) {
                        
                        /* Add in the table */
                        if($override) {
                            $override_url = $this->input->rest_replace($this->config->item('database', 'rest'), $db, $db_url);
                            $url = $this->input->rest_replace($this->config->item('table', 'rest'), $table, $override_url);
                        } else {
                            $url = $this->input->rest_replace($this->config->item('table', 'rest'), $table, $db_url);
                        }
                        
                        $arrChildren[] = array(
                            'plain' => $table,
                            'url' => $url,
                        );
                    }
                }
                    
                /* Add in the table data */
                $arrReturn[] = array(
                    'plain' => $db,
                    'url' => $db_url,
                    'tables' => $arrChildren,
                );
            }
            
            /* Unset the database */
            $this->db()->database = '';
        }
        return $arrReturn;
    }
    
    
    
    
    
    
    /**
     * Fetch Table
     * 
     * Fetches the table from the database
     * 
     * @param string $db
     * @param string $table
     * @param number $limit
     * @param number $offset
     * @return array
     */
    public function fetch_table($db, $table, $limit = null, $offset = null, $add_blank_row = true) {
        /* Make sure that $db and $table are both strings */
        if(!is_string($db)) { return false; }
        if(!is_string($table)) { return false; }
        
        /* Get limit */
        if(is_null($limit) || !is_numeric($limit)) { $limit = $this->config->item('default_limit'); }
        if(is_null($offset) || !is_numeric($offset)) { $offset = $this->config->item('default_offset'); }
        
        /* Set the database */
        $this->_use_database($db);
        
        /* Query the database */
        $this->db()->select()
            ->from($table)
            ->limit($limit, $offset);
        $arrDb = $this->db()->qAssoc();
        
        /* Make sure it's an array */
        if($arrDb === false) { $arrDb = array(); }
        
        /* Get the default values */
        if($add_blank_row) {
            $arrBlank = $this->db()->qFields($table);
            
            if(is_array($arrBlank) && count($arrBlank) > 0) {
                $arrTmp = array();
                foreach($arrBlank as $blank) {
                    /* Default value */
                    $value = '';
                    
                    if($blank['auto']) {
                        $value = $this->lang->line('database_auto_inc');
                    } elseif($blank['datatype'] == 'int') {
                        $value = 0;
                    } elseif($blank['datatype'] == 'enum') {
                        $value = array(
                            'default' => $blank['default'],
                            'options' => $blank['enum'],
                        );
                    } elseif($blank['null']) {
                        $value = null;
                    } else {
                        $value = $blank['default'];
                    }
                    
                    /* Add in the value */
                    $arrTmp[$blank['field']] = $value;
                }
                $arrDb[] = $arrTmp;
            }
        }
        return $arrDb;
        
    }
    
    
    
    
    
    
    /**
     * Get DB Types
     * 
     * Get the DB types.  By default, it checks
     * that the database is available on this
     * server
     * 
     * @param bool verify
     * @return array
     */
    public function get_db_types($verify = true) {
        $arrDb = $this->_arrDBTypes;
        if($verify) {
            if(count($arrDb) > 0) {
                /* Cycle through and remove ones that don't exist */
                foreach($arrDb as $key => $value) {
                    if(can_connect_db($key) === false) {
                        unset($arrDb[$key]);
                    }
                }
            }
        }
        return $arrDb;
    }
    
    
    
    
    
    
    
    /**
     * Get Type
     * 
     * Gets the connection type
     * 
     * @return string
     */
    public function get_type() { return $this->db()->dbdriver; }
    
    
    
    
    
    
    
    /**
     * Logout
     * 
     * Logout from the (given) database 
     * 
     * @param string $connection
     */
    public function logout($connection = null) {
        if(!is_null($connection)) {
            /* Logout from the specified DB */
            $arrSession = $this->session->userdata($this->config->item('session_login'));
            
            if(array_key_exists($connection, $arrSession)) {
                unset($arrSession[$connection]);
            }
            
            if(count($arrSession) == 0) {
                /* Nothing left - just destroy it */
                $this->session->unset_userdata($this->config->item('session_login'));
            } else {
                /* Connections are left - resave it */
                $this->session->set_userdata($this->config->item('session_login'), $arrSession);
            }
        } else {
            /* Logout from all */
            $this->session->unset_userdata($this->config->item('session_login'));
        }
    }
    
    
    
    
    
    
    /**
     * Queries
     * 
     * Fetch a history of all the queries made
     * 
     * @return array 
     */
    public function queries() {
        $arrHistory = $this->db()->queries;
        
        if(!is_array($arrHistory)) { $arrHistory = array(); }
        
        return $arrHistory;
    }
    
    
    
    
    
    
    
    /**
     * Reorder Connection
     * 
     * Reorders the connections.  Also offers opportunity
     * to save
     * 
     * @param array $arrSort
     * @param bool $save
     * @return array
     */
    public function reorder_connection(array $arrSort = array(), $save = true) {
        
        $arrConnections = $this->fetch_connections();
        
        if(count($arrConnections) > 0 && count($arrSort) > 0) {
            
            $arrConnections = sort_by_array($arrConnections, $arrSort);
            
            if($save) {
                /* Save the connection order */
                
                $this->session->set_userdata($this->config->item('session_login'), $arrConnections);
            }
            
        }
        
        return $arrConnections;
    }

        
    
    
    
    
    
    /**
     * Save Connection
     * 
     * Saves the connection to the session
     * 
     * @param string $id
     * @param string $username
     * @param string $password
     * @param string $server
     * @param string $type
     * @param string $port
     * @param string $name
     */
    public function save_connection($id, $username, $password, $server, $type, $port, $name) {
        
        /* Get the existing session data */
        $arrSession = $this->session->userdata($this->config->item('session_login'));
        
        if($arrSession === false) { $arrSession = array(); }
        
        /* Save the session */
        $arrData = array(
            'name' => $name,
            'username' => $username,
            'password' => $password,
            'server' => $server,
            'type' => $type,
            'port' => $port,
        );
        
        $arrSession[$id] = $arrData;

        /* Save session */
        $this->session->set_userdata($this->config->item('session_login'), $arrSession);
    }
    
    
    
    
    
    
    /**
     * Use Database
     * 
     * Sets the database to use
     * 
     * @param string $db
     * @return bool
     */
    public function use_database($db) { return $this->_use_database($db); }
    
    
    
    
    
    
    /**
     * Verify Saved Login
     * 
     * Checks that there's at least one valid
     * login stored in the session
     * 
     * @return bool
     */
    public function verify_saved_login() {
        /* Get the session values */
        $arrLogin = $this->session->userdata($this->config->item('session_login'));
        
        if(is_array($arrLogin) && count($arrLogin) > 0) {
            
            $objCI = &get_instance();
            
            foreach($arrLogin as $name => $data) {
                /* Get the DB values */
                $arrDb = $this->_set_db_values($data['server'], $data['username'], $data['password'], $data['type'], $data['port']);
                
                /* Generate an ID for it */
                $id = md5(serialize($arrDb));
                
                $connection = $this->load->database($arrDb, $id);
                
                /* Check it's a valid connection */
                if(is_resource($connection->conn_id)) {
                    /* Save the connection */
                    $this->_arrConnections[$id] = $connection;
                }
            }
            
            /* Are there any valid connections */
            return count($this->_arrConnections) > 0;
            
        }
        
        /* Invalid database */
        return false;
    }

    
    
    
    
    
    /**
     * Verify Login
     * 
     * Verify the database connection
     * 
     * @param string $username
     * @param string $password
     * @param string $server
     * @param string $type
     * @param string $port
     * @return string/boolean
     */
    public function verify_login($username = null, $password = null, $server = null, $type = null, $port = null) {
        
        /* If type is null, fail by default as CI can't connect */
        if(is_null($type)) { return false; }
        
        /* Get the DB values */
        $arrDb = $this->_set_db_values($server, $username, $password, $type, $port);
        
        /* Connect to the database */
        $this->load->database($arrDb);
        
        /* If resource, valid connection */
        if(is_resource($this->db->conn_id)) {
            /* Generate an ID for it */
            $id = md5(serialize($arrDb));
            return $id;
        } else {
            /* Invalid - clear the session */
            //$this->logout();
            return false;
        }
        
    }


}
?>